package com.pkuse.bjousystem.repository.student;

import com.pkuse.bjousystem.dto.student.XueShengJiHuaJianYaoDTO;
import com.pkuse.bjousystem.dto.student.XueShengRuXueNianDuXueQiDTO;
import com.pkuse.bjousystem.entity.student.Student;
import com.pkuse.bjousystem.dto.student.XueShengXinXiDTO;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface StudentRepository extends CrudRepository<Student, String> {

    @Query("select new com.pkuse.bjousystem.dto.student.XueShengXinXiDTO(a.xm,a.xh,b.sfzh,b.yddh,c.dzzczh,c.bynd,byxqdmb.xqmc," +
            "d.zymc,e.zyccmc,a.gzh,a.bdm,f.bmc,g.xjztmc,a.nd,rxxqdmb.xqmc) " +
            "from Student a " +
            "left join XueShengJiBenQingKuang b on a.xh = b.xh " +
            "left join XueShengBiYe c on a.xh=c.xh " +
            "left join ZhuanYeDaiMa d on a.zydm=d.zydm " + //--专业代码表
            "left join ZhuanYeCengCiDaiMa e on a.zyccdm=e.zyccdm " + //--专业层次代码表
            "left join BanJiXinXi f on a.bdm=f.bdm " + //--班级代码表
            "left join XueJiZhuangTaiDaiMa g on a.xjztdm=g.xjztdm " + //--学籍状态代码表
            "left join XueQiDaiMa byxqdmb on byxqdmb.xqdm=c.byxq " +
            "left join XueQiDaiMa rxxqdmb on rxxqdmb.xqdm=a.xqdm " +
            "where " +
            "a.xh = ?1")
    XueShengXinXiDTO findStudentDetials(String xh);

    @Query("select distinct new com.pkuse.bjousystem.dto.student.XueShengRuXueNianDuXueQiDTO" +
            "(xsb.nd, xsb.xqdm, xqdmb.xqmc) " +
            "from Student xsb " +
            "inner join XueQiDaiMa xqdmb on xsb.xqdm = xqdmb.xqdm " +
            "where (:xxdm is null or xsb.xxdm=:xxdm) and xsb.xjztdm='1'")
    List<XueShengRuXueNianDuXueQiDTO> listRuXueXueQiForEnrollment(@Param("xxdm") String xxdm);


    @Query("select new com.pkuse.bjousystem.dto.student.XueShengJiHuaJianYaoDTO" +
            "(xsb.xh,xsb.xm,zydmb.zymc,xsb.gzh,zyccdmb.zyccmc,xslxdmb.xslxmc, " +
            "xsb.zygznd,xqdmb.xqmc,zygzb.byxf,zygzb.zyxf) " +
            "from Student xsb " +
            "left join ZhuanYeGuiZe zygzb on xsb.gzh=zygzb.gzh " +
            "left join ZhuanYeDaiMa zydmb on xsb.zydm=zydmb.zydm " +
            "left join ZhuanYeCengCiDaiMa zyccdmb on zyccdmb.zyccdm=xsb.zyccdm  " +
            "left join XueShengLeiXingDaiMa xslxdmb on xslxdmb.xslxdm=xsb.xslxdm " +
            "left join XueQiDaiMa xqdmb on xsb.zygzxq=xqdmb.xqdm " +
            "where xsb.xh=:xh")
    XueShengJiHuaJianYaoDTO getStudentPlanBreif(@Param("xh") String xh);

}
